﻿CREATE procedure admin.s_help
	@objname nvarchar(776),		-- object name we're after
	@object_schema sysname,
	@FileName sysname
as
	-- PRELIMINARY
	set nocount on
	declare	@dbname	sysname
		,@no varchar(35), @yes varchar(35), @none varchar(35)
	select @no = 'no', @yes = 'yes', @none = 'none'

	-- If no @objname given, give a little info about all objects.
	DECLARE @SQL nvarchar(max)
	if @objname is null
	begin
		-- DISPLAY ALL SYSOBJECTS --
		SET @SQL = '<table>
		<thead><tr><th>Name<th>Owher<th>Object_Type</THEAD>
		'
        select
            @SQL = @SQL + '<tr><td>'          + o.name+
            '<td>'         + user_name(ObjectProperty( object_id, 'ownerid'))+
            '<td>'   + substring(v.name,5,31)
        from sys.all_objects o, master.dbo.spt_values v
        where o.type = substring(v.name,1,2) collate database_default and v.type = 'O9T'
        

		print @SQL

		-- DISPLAY ALL USER TYPES
		select
			'User_type'	= name,
			'Storage_type'	= type_name(system_type_id),
			'Length'		= max_length,
			'Prec'		= Convert(int,TypeProperty(user_type_id, 'precision')),
			'Scale'		= Convert(int,TypeProperty(user_type_id, 'scale')),
			'Nullable'		= case when is_nullable = 1 then @yes else @no end,
			'Default_name'	= isnull(object_name(default_object_id), @none),
			'Rule_name'		= isnull(object_name(rule_object_id), @none),
			'Collation'		= collation_name
		from sys.types
		where user_type_id > 256
		order by name

		return(0)
	end

	-- Make sure the @objname is local to the current database.
	select @dbname = parsename(@objname,3)
	if @dbname is null
		select @dbname = db_name()
	else if @dbname <> db_name()
		begin
			raiserror(15250,-1,-1)
			return(1)
		end

	-- @objname must be either sysobjects or systypes: first look in sysobjects
	declare @objid int
	declare @sysobj_type char(2)
	select @objid = object_id, @sysobj_type = type from sys.all_objects where object_id = object_id(@objname)

	-- IF NOT IN SYSOBJECTS, TRY SYSTYPES --
	if @objid is null
	begin
		-- UNDONE: SHOULD CHECK FOR AND DISALLOW MULTI-PART NAME
		select @objid = type_id(@objname)

		-- IF NOT IN SYSTYPES, GIVE UP
		if @objid is null
		begin
			raiserror(15009,-1,-1,@objname,@dbname)
			return(1)
		end

		-- DATA TYPE HELP (prec/scale only valid for numerics)
		select
			'Type_name'	= name,
			'Storage_type'	= type_name(system_type_id),
			'Length'		= max_length,
			'Prec'			= Convert(int,TypeProperty(user_type_id, 'precision')),
			'Scale'			= Convert(int,TypeProperty(user_type_id, 'scale')),
			'Nullable'			= case when is_nullable=1 then @yes else @no end,
			'Default_name'	= isnull(object_name(default_object_id), @none),
			'Rule_name'		= isnull(object_name(rule_object_id), @none),
			'Collation'		= collation_name
		from sys.types
		where user_type_id = @objid

		return(0)
	end

	-- FOUND IT IN SYSOBJECT, SO GIVE OBJECT INFO

--<style type="text/css">
--	table {border-collapse:collapse}
--	table thead tr {background:lightgrey}
--</style>

		SET @SQL = '
<table border="1" style="background-color:Black;color:White">
'
        select
            @SQL = @SQL + '<tr><th colspan="2">'+user_name(ObjectProperty( object_id, 'ownerid'))+'.'+ o.name+
            ': '   + substring(v.name,5,31)
	from sys.all_objects o, master.dbo.spt_values v
	where o.object_id = @objid and o.type = substring(v.name,1,2) collate database_default and v.type = 'O9T'
		SET @SQL = @SQL+'
</table>'

	-- DISPLAY COLUMN IF TABLE / VIEW
	if exists (select * from sys.all_columns where object_id = @objid)
	begin

		-- SET UP NUMERIC TYPES: THESE WILL HAVE NON-BLANK PREC/SCALE
		-- There must be a ',' immediately after each type name (including last one),
		-- because that's what we'll search for in charindex later.
		declare @precscaletypes nvarchar(150)
		select @precscaletypes = N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney,date,time,datetime2,datetimeoffset,'

		-- INFO FOR EACH COLUMN
		SET @SQL = @SQL+'<table border="1">
		<thead><tr><th>Column<th>Type<th>Length<th>Prec<th>Scale<th>Nullable<th>Computed</THEAD>
'
		select
		@SQL=@SQL+
			'<tr><th>'+ name+
			'<td>'					+ type_name(user_type_id)+
			'<td>' +convert(varchar, max_length)+
			-- for prec/scale, only show for those types that have valid precision/scale
			-- Search for type name + ',', because 'datetime' is actually a substring of 'datetime2' and 'datetimeoffset'
			'<td>'+case when charindex(type_name(system_type_id) + ',', @precscaletypes) > 0
										then convert(char(5),ColumnProperty(object_id, name, 'precision'))
										else '     ' end+
			'<td>'+ case when charindex(type_name(system_type_id) + ',', @precscaletypes) > 0
										then convert(char(5),OdbcScale(system_type_id,scale))
										else '     ' end+
			'<td>'				+ case when is_nullable = 0 then @no else @yes end+
			'<td>'	+ case when ColumnProperty(object_id, name, 'IsComputed') = 0 then @no else @yes end
		from sys.all_columns where object_id = @objid
		SET @SQL = @SQL+'
</table>'

	end

	-- DISPLAY ANY PARAMS
	if exists (select * from sys.all_parameters where object_id = @objid)
	begin
		-- INFO ON PROC PARAMS
		SET @SQL = @SQL+'<table border="1">
		<thead><tr><th>Parameter<th>Type<th>Length<th>Prec<th>Scale</THEAD>
'
		select
		@SQL=@SQL+
			'<tr><th>'+name+
			'<td>'+ISNULL(type_name(user_type_id),'')+
			'<td>'+ISNULL(CONVERT(varchar, max_length),'')+
			'<td>'+ISNULL(CONVERT(varchar, case when type_name(system_type_id) = 'uniqueidentifier' then precision
								else OdbcPrec(system_type_id, max_length, precision) end),'')+
			'<td>'	+ ISNULL(CONVERT(varchar, OdbcScale(system_type_id, scale)),'')

		from sys.all_parameters where object_id = @objid
		SET @SQL = @SQL+'
</table>'
	end

	-- DISPLAY TABLE INDEXES & CONSTRAINTS
	if @sysobj_type in ('S ','U ')
	begin
		DECLARE @Indx nvarchar(max)
		EXEC admin.s_helpindex @objname,@Indx OUT
		SET @SQL = @SQL+ISNULL(@Indx,'')
		DECLARE @Xml xml
		EXEC admin.s_helpPKs @objname,@Xml OUT
		SET @SQL = @SQL+ISNULL(CONVERT(varchar(max), @Xml),'')
		EXEC admin.s_helpFKs @objname,@Xml OUT
		SET @SQL = @SQL+ISNULL(CONVERT(varchar(max), @Xml),'')
	end
	
	if @sysobj_type = 'U' and 
		 exists(SELECT * FROM sys.triggers AS tr INNER JOIN sys.tables AS t ON tr.parent_id = t.object_id WHERE t.object_id = @objid)
	begin
		SET @SQL = @SQL+'<table border="1">
		<thead><tr><th>Trigger<th>Type</THEAD>
'
		SELECT 
		@SQL=@SQL+
			'<tr><th>'+		tr.name+
			'<td>'+dbo.clr_RegEx_MatchGroup(object_definition(tr.object_id),'FOR\s+(.+?)\s+AS\s',1)
		FROM         sys.triggers AS tr INNER JOIN
    sys.tables AS t ON tr.parent_id = t.object_id
    WHERE t.object_id = @objid
		SET @SQL = @SQL+'
</table>'
	end
if exists(            select * from sys.objects obj, sysdepends deps
				where obj.object_id = deps.id and deps.depid = @objid
					group by obj.name
)begin
		SET @SQL = @SQL+'<table border="1">
		<thead><tr><th>Depend on '+@objname+'</THEAD>
'
SELECT @SQL=@SQL+'<tr><td>'+name FROM(
            select distinct obj.name 
            from sys.objects obj, sysdepends deps
				where obj.object_id = deps.id and deps.depid = @objid
					group by obj.name)T
		SET @SQL = @SQL+'
</table>'
end

EXEC admin.s_Help_DependsOn_Xml @objname,@object_schema,@Xml OUT
SET @SQL = @SQL+ISNULL(CONVERT(varchar(max), @Xml),'')

PRINT @SQL
EXEC sp_AppendToFile @FileName,@SQL	
	return (0) -- sp_help

